
package com.hitrust.gs.business;

import java.util.ArrayList;

import com.hitrust.gs.dao.GSWarehouseStorageConfigDAO;
import com.hitrust.gs.form.ProductNotPurchaseForm;
import com.hitrust.gs.lib.GSLogWriter;
import com.hitrust.gs.service.GSBaseProcesser;
import com.hitrust.gs.util.date.DateUtil;
import com.hitrust.gs.util.exception.GSException;
import com.hitrust.gs.vo.GSAllWarehouseCodeVO;
import com.hitrust.gs.vo.GSProductCountVO;
import com.hitrust.gs.vo.ProductIdVO;
import com.hitrust.gs.vo.ProductMapOldVO;
import com.hitrust.gs.vo.ProductTiptopStockVO;
import com.hitrust.gs.vo.ProductUpdatePicTaskVO;
import com.hitrust.gs.vo.ProductUpdatePicVO;
import com.hitrust.gs.vo.WarehouseCodeVO;
import com.hitrust.gs.vo.WarehouseMaxStorageCofigVO;


/**
 * @author wuyang
 * 
 * TODO To change the template for this generated type comment go to Window -
 * Preferences - Java - Code Style - Code Templates
 */
public class GSProductTiptopStockProcesser implements GSBaseProcesser {
      
    
    /**
     * 查询产品最高库存设置数量
     * 
     * @param operatorBean
     * @param vo
     * @return
     * @throws GSException
     */
    public int getWareHouseMaxStorageConfigCount(WarehouseMaxStorageCofigVO vo) throws GSException {
    	GSWarehouseStorageConfigDAO wscDao = new GSWarehouseStorageConfigDAO();
        String tSql = new StringBuffer("select count(*) from (").append(getSql(vo)).append(")").toString();
        try {
            return wscDao.queryRowNum(tSql, null);
        } catch (Exception e) {
            throw new GSException(GSException.E19999, GSException.E19999_MSG);
        }

    }
    
    /**
     * 产品不可采购可出库管理 获得总条数
     * 
     * @param operatorBean
     * @param vo
     * @return
     * @throws GSException
     */
    public int getProductNotPurchaseCount(ProductNotPurchaseForm vo) throws GSException {
    	GSWarehouseStorageConfigDAO wscDao = new GSWarehouseStorageConfigDAO();
        String tSql = new StringBuffer("select count(*) from (").append(getNotPurSql(vo)).append(")").toString();
        try {
            return wscDao.queryRowNum(tSql, null);
        } catch (Exception e) {
            throw new GSException(GSException.E19999, GSException.E19999_MSG);
        }

    }
    
    /**
     * 产品不可采购可出库管理 获得查询sql
     * 
     * @param operatorBean
     * @param vo
     * @return
     * @throws GSException
     */
    private String getNotPurSql(ProductNotPurchaseForm vo){
    	StringBuffer sb = new StringBuffer();
    	sb.append("select a.id, b.CONCENTRICITY_CODE, b.NAME, b.SPEC, b.MODEL,b.DRAWING_PIECE_CODE,  c.name providername,areatype,type_name, " +
    			  "a.ENABLED_TIME,a.status, a.AUDITING_STATUS ,a.CREATE_USER,a.CREATE_TIME,AUDITING_USER,AUDITING_TIME");
    	sb.append(" from t_product b ,t_provider c, T_PRODUCT_UPDATE_PIC a, ");
    	sb.append("  (select a.product_id,  b.TYPE_NAME  areatype from t_product_type a,t_product_type_manager b where A.PRODUCT_TYPE = B.PRODUCT_TYPE  and UPPER_TYPE = 1000 and a.status = 1) type,");
    	sb.append("  ( select a.product_id,  b.TYPE_NAME   from t_product_bid a,t_product_type_manager b where A.PRODUCT_TYPE = B.PRODUCT_TYPE  and UPPER_TYPE = 2000 and a.status = 1) bid    ");
    	sb.append(" where b.provider_code = c.code and  a.product_id = b.id   and  b.id = type.product_id(+)  and  b.id = bid.product_id(+) ");
    	//集中编码
    	if(vo.getM_concentricity_code() != null && !"".equals(vo.getM_concentricity_code().trim())){
    		sb.append(" and b.CONCENTRICITY_CODE = '"+vo.getM_concentricity_code()+"'");
    	}
    	
    	// 产品名称
    	if(vo.getM_product_name() != null && !"".equals(vo.getM_product_name().trim())){
    		sb.append(" and b.NAME like '%"+vo.getM_product_name()+"%'");
    	}
    	
    	// 型号
    	if(vo.getM_model() != null && !"".equals(vo.getM_model().trim())){
    		sb.append(" and b.MODEL = '"+vo.getM_model()+"'");
    	}
    	
    	// 规格
    	if(vo.getM_spec() != null && !"".equals(vo.getM_spec().trim())){
    		sb.append(" and b.SPEC = '"+vo.getM_spec()+"'");
    	}
    	
    	// 图件号
    	if(vo.getM_drawing_piece_code() != null && !"".equals(vo.getM_drawing_piece_code().trim())){
    		sb.append(" and b.DRAWING_PIECE_CODE = '"+vo.getM_drawing_piece_code()+"'");
    	}
    	
    	// 供应商
    	if(vo.getM_provider_code() != null && !"".equals(vo.getM_provider_code().trim())){
    		sb.append(" and c.name like '%"+vo.getM_provider_code()+"%'");
    	}
    	
    	// 是否执行
    	if(vo.getM_status() != null && !"".equals(vo.getM_status().trim())){
    		sb.append(" and a.status = '"+vo.getM_status()+"'");
    	}
    	
    	// 审批状态
    	if(vo.getM_auditing_status() != null && !"".equals(vo.getM_auditing_status().trim())){
    		sb.append(" and a.AUDITING_STATUS = '"+vo.getM_auditing_status()+"'");
    	}
    	
    	// 启用开始日期
    	if(vo.getM_EnabledTimeStart() != null && !"".equals(vo.getM_EnabledTimeStart().trim())){
    		sb.append("  AND to_char(to_date(a.ENABLED_TIME,'YYYY-MM-DD HH24:mi:ss'),'YYYY-MM-DD') >= '"+vo.getM_EnabledTimeStart()+"'");
    	}
    	
    	// 启用结束日期
    	if(vo.getM_EnabledTimeEnd() != null && !"".equals(vo.getM_EnabledTimeEnd().trim())){
    		sb.append("   AND to_char(to_date(a.ENABLED_TIME,'YYYY-MM-DD HH24:mi:ss'),'YYYY-MM-DD') <= '"+vo.getM_EnabledTimeEnd()+"'");
    	}
		return sb.toString();
    	
    }
    
    /**
     * 产品不可采购可出库管理   分页
     * 
     * @param operatorBean
     * @param vo
     * @return
     * @throws GSException
     */
    public ArrayList getProductNotPurchase(ProductNotPurchaseForm vo, int pageNo, int pageSize) throws GSException {
    	GSWarehouseStorageConfigDAO wscDao = new GSWarehouseStorageConfigDAO();
        String tSql = getNotPurSql(vo);
       
        ArrayList orderDetailList = null;
        try {
            orderDetailList = wscDao.executeQuery(new ProductUpdatePicVO(), tSql, null, pageNo, pageSize);
        } catch (Exception e) {
            throw new GSException(GSException.E20055, GSException.E20055_MSG);
        }

        return orderDetailList;
    }
    
    
    /**
     *  查询产品不可采购可出库   不分页
     * 
     * @param operatorBean
     * @param vo
     * @return
     * @throws GSException
     */
    public ArrayList getProductNotPurchaseAll(ProductNotPurchaseForm vo) throws GSException {
    	GSWarehouseStorageConfigDAO wscDao = new GSWarehouseStorageConfigDAO();
        String tSql = getNotPurSql(vo);
       
        ArrayList orderDetailList = null;
        try {
            orderDetailList = wscDao.executeQuery(new ProductUpdatePicVO(), tSql, null);
        } catch (Exception e) {
            throw new GSException(GSException.E20055, GSException.E20055_MSG);
        }

        return orderDetailList;
    }
    
    
    
    
    private String getSql(WarehouseMaxStorageCofigVO vo){
    	StringBuffer sb = new StringBuffer();
    	sb.append("select distinct b.CONCENTRICITY_CODE,  b. name, b.PRODUCT_UNIT, b.UNIT_PRICE,  b.spec," +
    				" b.model, b.drawing_piece_code, c.name providername, d.max_count,  a.name orgname  ,d.last_update_time, d.status");
    	sb.append("    FROM t_product b,  t_provider c,  t_warehouse a, t_warehouse_max_storage_config d ");
    	sb.append(" WHERE   b.provider_code = c.code  AND D.PRODUCT_CODE = b.CONCENTRICITY_CODE and  a.code = d.WAREHOUSE_CODE");
    	
    	//-订货单位
    	if (vo.m_currentWarehouseCode != null && !"".equals(vo.m_currentWarehouseCode )&& !"00".equals(vo.m_currentWarehouseCode )) {
			sb.append("  and a.code = '"+vo.m_currentWarehouseCode+"'");
		}
    	//-产品名称
    	if (vo.m_product_name != null && !"".equals(vo.m_product_name )) {
    		sb.append("  and  b.name = '"+vo.m_product_name+"'");
		}
    	
    	//-规    格
    	if (vo.m_spec != null && !"".equals(vo.m_spec )) {
    		sb.append("  and    b.spec = '"+vo.m_spec+"'");
		}
    	
    	//-型    号
    	if (vo.m_model != null && !"".equals(vo.m_model ) ) {
    		sb.append("  and    b.model = '"+vo.m_model+"'");
		}
    	
    	//--图 件 号
    	if (vo.m_drawing_piece_code != null && !"".equals(vo.m_drawing_piece_code )) {
    		sb.append("  and    b.drawing_piece_code = '"+vo.m_drawing_piece_code+"'");
		}
    	
    	//--集中编码
    	if (vo.m_concentricity_code != null && !"".equals(vo.m_concentricity_code )) {
    		sb.append("  and    b.CONCENTRICITY_CODE = '"+vo.m_concentricity_code+"'");
		}
    	
    	//--供应商
    	if (vo.m_provider_code != null && !"".equals(vo.m_provider_code )) {
    		sb.append("  and    c.name = '"+vo.m_provider_code+"'");
		}
    	//--状态
    	if (vo.m_status != null && !"".equals(vo.m_status)) {
			sb.append(" and D.STATUS = "+vo.m_status);
		}
    	sb.append("   order by CONCENTRICITY_CODE ");
		return sb.toString();
    	
    }
    
    
    /**
     * 查询产品最高库存设置   分页
     * 
     * @param operatorBean
     * @param vo
     * @return
     * @throws GSException
     */
    public ArrayList getWareHouseMaxStorageConfig(WarehouseMaxStorageCofigVO vo, int pageNo, int pageSize) throws GSException {
    	GSWarehouseStorageConfigDAO wscDao = new GSWarehouseStorageConfigDAO();
        String tSql = getSql(vo);
       
        ArrayList orderDetailList = null;
        try {
            orderDetailList = wscDao.executeQuery(new ProductTiptopStockVO(), tSql, null, pageNo, pageSize);
        } catch (Exception e) {
            throw new GSException(GSException.E20055, GSException.E20055_MSG);
        }

        return orderDetailList;
    }
    
    
    /**
     * 查询产品最高库存设置
     * 
     * @param operatorBean
     * @param vo
     * @return
     * @throws GSException
     */
    public ArrayList getWareHouseMaxStorageConfigAll(WarehouseMaxStorageCofigVO vo) throws GSException {
    	GSWarehouseStorageConfigDAO wscDao = new GSWarehouseStorageConfigDAO();
        String tSql = getSql(vo);
       
        ArrayList orderDetailList = null;
        try {
            orderDetailList = wscDao.executeQuery(new ProductTiptopStockVO(), tSql, null);
        } catch (Exception e) {
            throw new GSException(GSException.E20055, GSException.E20055_MSG);
        }

        return orderDetailList;
    }
    
    
    /**
     * 根据传入的sql查询,是否有记录
     * 
     * @param operatorBean
     * @param vo
     * @return
     * @throws GSException
     */
    public int isExistRow(String sql) throws GSException {
    	GSWarehouseStorageConfigDAO wscDao = new GSWarehouseStorageConfigDAO();
        String tSql = new StringBuffer("select count(*) from (").append(sql).append(")").toString();
        try {
            return wscDao.queryRowNum(tSql, null);
        } catch (Exception e) {
            throw new GSException(GSException.E19999, GSException.E19999_MSG);
        }

    }
    
    

    
    /**
     * 添加仓库产品最高库存设置
     * 
     * @param 最高库存设置对象
     * 
     * @return boolean
     */
    public boolean insertWarehouseMaxStorageConfig(WarehouseMaxStorageCofigVO vo) throws GSException {
    	GSWarehouseStorageConfigDAO wscDao = new GSWarehouseStorageConfigDAO();
    	return wscDao.insertWarehouseMaxStorageConfig(vo);
    }
    
    
    /**
     * 修改仓库产品最高库存设置
     * 
     * @param 最高库存设置对象
     * 
     * @return boolean
     */
    public boolean updateWarehouseMaxStorageConfig(WarehouseMaxStorageCofigVO vo) throws GSException {
    	GSWarehouseStorageConfigDAO wscDao = new GSWarehouseStorageConfigDAO();
    	return wscDao.updateWarehouseMaxStorageConfig(vo);
    }
    
    
    /**
     * 查询仓库执行配置是否存在
     * @param warehouseCode         客修分公司编码
     * @return                      int
     * @throws GSException
     */
    public int isExistWarehouseStorageConfig(String warehouseCode) throws GSException {
    	GSWarehouseStorageConfigDAO wscDao = new GSWarehouseStorageConfigDAO();
		String tSql = "select * from t_warehouse_storage_config where STATUS = 1 and warehouse_code = '"+warehouseCode+"'";
		
		try{
			return  wscDao.queryRowNum(tSql, null);
		
		}catch(Exception e){
			GSLogWriter.error(this,e.toString());
			throw new GSException(GSException.E19999,GSException.E19999_MSG);
		}
		
	
    }
    
    /**
     * 根据产品号查询分公司
     *
     * @throws GSException
     */
    public ArrayList selectWarehouseCode(String productCode,String wareHouseCode) throws GSException {
    	GSWarehouseStorageConfigDAO wscDao = new GSWarehouseStorageConfigDAO();
        StringBuffer sb = new StringBuffer();
        sb.append("select WAREHOUSE_CODE,MAX_COUNT from t_warehouse_max_storage_config where STATUS = 1 and product_code = '"+productCode+"' and WAREHOUSE_CODE = '"+wareHouseCode+"'");
        
        ArrayList warehouseCodeList = null;
        try {
        	warehouseCodeList = wscDao.executeQuery(new WarehouseCodeVO(), sb.toString(), null);
        } catch (Exception e) {
            throw new GSException(GSException.E20055, GSException.E20055_MSG);
        }

        return warehouseCodeList;
    }
    
    
    /**
     * 单个分公司库存总和 
     * 
     * @throws GSException
     */
    public ArrayList selectProductCount(String productCode,String warehouseCode) throws GSException {
    	GSWarehouseStorageConfigDAO wscDao = new GSWarehouseStorageConfigDAO();
        StringBuffer sb = new StringBuffer();
        sb.append("select  sum(PRODUCT_COUNT)  PRODUCT_COUNT from T_PRODUCT_STORAGE tps,t_warehouse tw where tps. WAREHOUSE_CODE = tw.code and  tw. code LIKE '"+warehouseCode+"%'");
        sb.append(" and tps.product_code='"+productCode+"'");
        
        ArrayList warehouseCodeList = null;
        try {
        	warehouseCodeList = wscDao.executeQuery(new GSProductCountVO(), sb.toString(), null);
        } catch (Exception e) {
            throw new GSException(GSException.E20055, GSException.E20055_MSG);
        }

        return warehouseCodeList;
    }
    
    
    
    /**
     * 各分仓的库存
     * 
     * @throws GSException
     */
    public ArrayList selectAllProductCount(String productCode,String warehouseCode) throws GSException {
    	GSWarehouseStorageConfigDAO wscDao = new GSWarehouseStorageConfigDAO();
        StringBuffer sb = new StringBuffer();
        sb.append("select  distinct sum(PRODUCT_COUNT) over(partition by WAREHOUSE_CODE)  WAREHOUSE_Count , " +
        		"WAREHOUSE_CODE,WAREHOUSE_NAME from T_PRODUCT_STORAGE tps,t_warehouse tw where tps. WAREHOUSE_CODE = tw.code and " +
        		" tw. code LIKE '"+warehouseCode+"%'");
        sb.append(" and tps.product_code='"+productCode+"'");
        ArrayList warehouseCodeList = null;
        try {
        	warehouseCodeList = wscDao.executeQuery(new GSAllWarehouseCodeVO(), sb.toString(), null);
        } catch (Exception e) {
            throw new GSException(GSException.E20055, GSException.E20055_MSG);
        }

        return warehouseCodeList;
    }
    
    
    /**
     * 导入数据到不可采购仍然可以出库配置执行表
     * 
     * @param ProductUpdatePicVO
     * 
     * @return boolean
     */
    public boolean insertProductUpdatePic(ProductUpdatePicVO vo) throws GSException {
    	GSWarehouseStorageConfigDAO wscDao = new GSWarehouseStorageConfigDAO();
    	return wscDao.insertProductUpdatePic(vo);
    }


    /**
     * 更新数据到不可采购仍然可以出库配置执行表
     * 
     * @param ProductUpdatePicVO
     * 
     * @return boolean
     */
    public boolean updateProductUpdatePic(ProductUpdatePicVO vo) throws GSException {
    	GSWarehouseStorageConfigDAO wscDao = new GSWarehouseStorageConfigDAO();
    	return wscDao.updateProductUpdatePic(vo);
    }
    
    
    /**
     *审批通过
     * 
     * @param id
     * 
     * @return boolean
     */
    public boolean passProductUpdatePic(String id,String userName,String userId,String currTime) throws GSException {
    	GSWarehouseStorageConfigDAO wscDao = new GSWarehouseStorageConfigDAO();
    	return wscDao.passProductUpdatePic(id,userName,userId,currTime);
    }
    
    /**
     *审批不通过
     * 
     * @param id
     * 
     * @return boolean
     */
    public boolean noPassProductUpdatePic(String id,String userName,String userId,String currTime) throws GSException {
    	GSWarehouseStorageConfigDAO wscDao = new GSWarehouseStorageConfigDAO();
    	return wscDao.noPassProductUpdatePic(id,userName,userId,currTime);
    }
    
    
    
    /**
     * 已审批通过但是没有执行的记录进行判断，
     * 并且启用日期在当前时间内
     * @return
     * @throws GSException
     */
    public ArrayList selectProductPic() throws GSException {
    	GSWarehouseStorageConfigDAO wscDao = new GSWarehouseStorageConfigDAO();
        StringBuffer sb = new StringBuffer();
        sb.append("select id, product_id,pic  from T_PRODUCT_UPDATE_PIC where AUDITING_STATUS = '1' and STATUS = '0' ");
        String curDate = DateUtil.getCurrentDate("yyyy-MM-dd HH:mm:ss");
        sb.append("and to_char( to_date(ENABLED_TIME,'YYYY-MM-DD HH24:mi:ss'),'YYYY-MM-DD HH24:mi:ss') < '"+curDate+"'");
        ArrayList warehouseCodeList = null;
        try {
        	warehouseCodeList = wscDao.executeQuery(new ProductUpdatePicTaskVO(), sb.toString(), null);
        } catch (Exception e) {
            throw new GSException(GSException.E20055, GSException.E20055_MSG);
        }

        return warehouseCodeList;
    }
    
    
    /**
     * 更新状态已执行
     * 
     * @param ProductUpdatePicVO
     * 
     * @return boolean
     */
    public boolean updateStatus(String id) throws GSException {
    	GSWarehouseStorageConfigDAO wscDao = new GSWarehouseStorageConfigDAO();
        boolean bl = false;
        StringBuffer sb = new StringBuffer();
        sb.append(" update T_PRODUCT_UPDATE_PIC set ");
        sb.append(" STATUS = '1'");
        sb.append(" where ");
        sb.append(" id = '"+id+"'");
          
        try {
                bl = wscDao.executeUpdate(sb.toString(), null, null, "");
            } catch (Exception e) {
                throw new GSException(GSException.E20042, GSException.E20042_MSG);
            }
        return bl;
    }
    
    
    /**
     * 更改产品表中的pic的值
     * 
     * @param 
     * 
     * @return boolean
     */
    public boolean updateProductPic(String productId,String pic) throws GSException {
    	GSWarehouseStorageConfigDAO wscDao = new GSWarehouseStorageConfigDAO();
        boolean bl = false;
        StringBuffer sb = new StringBuffer();
        sb.append(" update T_PRODUCT set ");
        sb.append(" pic = '"+pic+"'");
        sb.append(" where ");
        sb.append(" id = '"+productId+"'");
          
        try {
                bl = wscDao.executeUpdate(sb.toString(), null, null, "");
            } catch (Exception e) {
                throw new GSException(GSException.E20042, GSException.E20042_MSG);
            }
        return bl;
    }
    
    /**
     * 查找产品id
     * 
     * @param CONCENTRICITY_CODE
     * 
     * @return boolean
     */
    public ArrayList selectProductId(String concentricityCode) throws GSException {
    	GSWarehouseStorageConfigDAO wscDao = new GSWarehouseStorageConfigDAO();
       
        StringBuffer sb = new StringBuffer();
        sb.append(" select id from T_PRODUCT  where ");
        sb.append(" CONCENTRICITY_CODE = '"+concentricityCode+"'");
          
        ArrayList warehouseCodeList = null;
        try {
        	warehouseCodeList = wscDao.executeQuery(new ProductIdVO(), sb.toString(), null);
        } catch (Exception e) {
            throw new GSException(GSException.E20055, GSException.E20055_MSG);
        }

        return warehouseCodeList;
    }
    
    
    /**
     * 导入数据到老库存产品对应表
     * 
     * @param ProductUpdatePicVO
     * 
     * @return boolean
     */
    public boolean insertProductMapOld(ProductMapOldVO vo) throws GSException {
    	GSWarehouseStorageConfigDAO wscDao = new GSWarehouseStorageConfigDAO();
    	return wscDao.insertProductMapOld(vo);
    }
}
